<?php
	include 'from_client_entities.php';	
	
	$in_stream = file_get_contents('php://input');
	$phpArray = json_decode($in_stream);
	
	$from_client=new Efrom_client();
	
	foreach ($phpArray as $key => $value) {
		switch($key){
			case type_operation:
			$from_client->type_operation=$value;
			break;
			case id_track_commit:
			$from_client->id_track_commit=str_replace("'", "",$value);
			break;
			case MTX_EMAIL:
			$from_client->MTX_EMAIL=str_replace("'", "",$value);
			break;
			case STX_USERID_CASE_SENSITIVE:
			$from_client->STX_USERID_CASE_SENSITIVE=str_replace("'", "''", $value);
			break;
			case STX_MD5_PASSWORD:
			$from_client->STX_MD5_PASSWORD=str_replace("'", "",$value);
			break;
			case PK_SESSION:
			$from_client->PK_SESSION=$value;
			break;
			case INT_KEY_REGISTER:
			$from_client->INT_KEY_REGISTER=$value;
			break;
			case LNG_LAST_MACRO_BOUND_REFRESH:
			$from_client->LNG_LAST_MACRO_BOUND_REFRESH=$value;
			break;
			case left_bound_latitude:
			$from_client->left_bound_latitude=$value;
			break;
			case right_bound_latitude:
			$from_client->right_bound_latitude=$value;
			break;
			case top_bound_latitude:
			$from_client->top_bound_latitude=$value;
			break;
			case bottom_bound_latitude:
			$from_client->bottom_bound_latitude=$value;
			break;
			case PK_COMMENT:
			$from_client->PK_COMMENT=$value;
			break;
			case PK_MESSAGE:
			$from_client->PK_MESSAGE=$value;
			break;
			case FK_MESSAGE:
			$from_client->FK_MESSAGE=$value;
			break;
			case LTX_COMMENT:
			$from_client->LTX_COMMENT==str_replace("'", "''", $value);
			break;
			case TMS_LAST_MESSAGE_UPDATE:
			$from_client->TMS_LAST_MESSAGE_UPDATE=$value;
			break;
			case TMS_LAST_COMMENT_UPDATE:
			$from_client->TMS_LAST_COMMENT_UPDATE=$value;
			break;
			case LNG_LATITUDE_IDX:
			$from_client->LNG_LATITUDE_IDX=$value;
			break;
			case LNG_LONGITUDE_IDX:
			$from_client->LNG_LONGITUDE_IDX=$value;
			break;
			case ms_up_to_expiration:
			$from_client->ms_up_to_expiration=$value;
			break;
			case LTX_MESSAGE:
			$from_client->LTX_MESSAGE=str_replace("'", "''", $value);
			break;
			case add_less_like_unlike:
			$from_client->add_less_like_unlike=$value;
			break;
			case where_preference:
			$from_client->where_preference=$value;
			break;
		}
	}
	
	error_reporting(0);
	$mysqli = new mysqli(HOST_DB, USER_DB, PASSWORD_DB, DB);
	if ($mysqli->connect_errno) {
		$macro=array();
		$macro[outcome_response]=OUTCOME_FAILED;
		$macro[type_operation]=$from_client->type_operation;
		$macro[error]=str_replace("??ERROR??", $mysqli->connect_error ,CONN_ERR_101);
		echo json_encode($macro);
		return;
	}
	$mysqli->autocommit(FALSE);
	
	try{
		switch($from_client->type_operation){
			case type_operation_ping:
			ping($from_client, $mysqli);
			break;
			case type_operation_registration:
			login_registration($from_client, $mysqli);
			break;
			case type_operation_refresh:
			refresh($from_client, $mysqli);
			break;
			case type_operation_login:
			login($from_client, $mysqli);
			break;
			case type_operation_logout:
			logout($from_client, $mysqli);
			break;
			case type_operation_commit_preference:
			commit_preference($from_client, $mysqli);
			break;
			case type_operation_commit_message:
			commit_message($from_client, $mysqli);
			break;
			case type_operation_commit_comment:
			commit_comment($from_client, $mysqli);
			break;
			case type_operation_code_registration:
			unlock_code_registration($from_client, $mysqli);
			break;
		}
		$mysqli->commit();
	}catch(Exception $e){
		$macro=array();
		$macro[outcome_response]=OUTCOME_FAILED;
		$macro[type_operation]=$from_client->type_operation;
		$macro[id_track_commit]=$from_client->id_track_commit;
		if ( strpos($e->getMessage(), '-') == 3 && !preg_match('/[A-Za-z]/', substr($e->getMessage(), 0, 3) ) ){
			$macro[error]=$e->getMessage();
		}else{
			$macro[error]=str_replace("??ERROR??", $e->getMessage() ,GENERIC_ERR_105);
		}
		ob_clean();
		echo json_encode($macro);
		$mysqli->rollback();
	} 
	$mysqli->close();
	
/*
 * 		REGISTRATION (LOGIN)
 * 
 */
	function login_registration ($from_client, $mysqli){
		
		$query="SELECT IF ( LOWER(MTX_EMAIL) = LOWER('$from_client->MTX_EMAIL'), 'EMAIL', IF (STX_USERID_CASE_SENSITIVE='$from_client->STX_USERID_CASE_SENSITIVE', 'USERID', 'OK') ) AS FOUND_IT " .
				" FROM USER WHERE LOWER(MTX_EMAIL) = LOWER('$from_client->MTX_EMAIL') OR STX_USERID_CASE_SENSITIVE='$from_client->STX_USERID_CASE_SENSITIVE' ";
		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102) );
		}
		
		$fetch=$retval->fetch_assoc();
		
		if ($fetch){
			if (strtoupper ($fetch["FOUND_IT"])=='EMAIL'){
				throw new Exception(EMAIL_EXISTS_151);
			}else if (strtoupper ($fetch["FOUND_IT"])=='USERID'){
				throw new Exception(NAME_EXISTS_152);
			}
		}
		
		$time_now=time();
		$key_int= $time_now % 10000;
		
		// Send the mail
		$textmail= str_replace("??CODE??", $key_int,BODYTEXT_MAIL_CODE);
		
		if(!mail($from_client->MTX_EMAIL,SUBJECT_MAIL_CODE, $textmail,HEADER_MAIL_CODE))
		{
		    throw new Exception(EMAIL_NOT_SENT_158);
		}
		
		$query="INSERT INTO USER (STX_USERID_CASE_SENSITIVE, STX_MD5_PASSWORD, TMS_CREATED, MTX_EMAIL, INT_KEY_REGISTER ) " .
				" VALUES ('$from_client->STX_USERID_CASE_SENSITIVE', '$from_client->STX_MD5_PASSWORD', $time_now, '$from_client->MTX_EMAIL', $key_int)";
		$retval->free();
		$retval = $mysqli->query($query);
		
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		
		$macro=array();
		$macro[outcome_response]=OUTCOME_OK;
		$macro[type_operation]=$from_client->type_operation;
		echo json_encode($macro);
	}
	
	/*
	 * LOGIN
	 * 
	 */
	function login ($from_client, $mysqli){

		$query="SELECT PK_USER, QTY_LAST_FAILED_TENTATIVES, STX_USERID_CASE_SENSITIVE, TMS_NEXT_LOGIN, FLC_USER_LOCKED, TMS_CREATED, FLC_NEED_CODE, FK_LAST_SESSION " .
				" FROM USER WHERE LOWER(MTX_EMAIL) = LOWER('$from_client->MTX_EMAIL') AND STX_MD5_PASSWORD='$from_client->STX_MD5_PASSWORD' ";
		$retval = $mysqli->query($query);
		$time_now=time();
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		
		$fetch=$retval->fetch_assoc();
		
		if ($fetch){
			$pk_userid=$fetch["PK_USER"];
			$pk_last_session=$fetch["FK_LAST_SESSION"];
			$failed=$fetch["QTY_LAST_FAILED_TENTATIVES"];
			$next_login=$fetch["TMS_NEXT_LOGIN"];
			$locked=$fetch["FLC_USER_LOCKED"];
			$need_code=$fetch["FLC_NEED_CODE"];
			$date_created=$fetch["TMS_CREATED"];
			if (strtoupper($locked) == 'Y'){
				throw new Exception(USER_LOCKED_153);
			}
			if ($failed > MAX_FAILED_LOGIN){
				throw new Exception(MAX_TRIED_154);
			}
			if ($time_now < $next_login){
				throw new Exception(str_replace("??DATE??", date("F j, Y, g:i a", $next_login) ,LOCKED_UNTIL_155) );
			}
			$free_next=MS_FREE_LOGIN;
			if ( strtoupper($need_code) == 'Y' && $time_now - $date_created > $free_next){
				throw new Exception(NEED_CODE_159);
			}
			$name=$fetch["STX_USERID_CASE_SENSITIVE"];
			$key_int= $time_now % 10000;
			
			$query = "INSERT INTO SESSION (FK_USER, TMS_START, TMS_END) " .
					" VALUES ( $pk_userid, $time_now, 0)";
					
			$retval->free();
			$retval = $mysqli->query($query);
			
			if (! $retval){
				throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
			}
			$pk_session=$mysqli->insert_id;
			$query ="UPDATE USER SET FK_LAST_SESSION=$pk_session, QTY_LOGINS = QTY_LOGINS + 1, QTY_LAST_FAILED_TENTATIVES = 0 " .
					" WHERE PK_USER = $pk_userid ";
						
			$retval = $mysqli->query($query);

			$query ="UPDATE SESSION SET FLC_STATUS='X', TMS_END = $time_now " .
					" WHERE PK_SESSION = $pk_last_session AND UPPER(FLC_STATUS) = 'O' ";
			$retval = $mysqli->query($query);
			//nessuna gestione dell'errore
			
			$macro=array();
			if (strtoupper($need_code) == 'Y' ){
				$macro[outcome_response]=OUTCOME_OK_BUT_WARNING;
				$macro[error]=str_replace("??DATE??", date( "F j, Y, g:i a", $date_created+$free_next ),WARNING_NEED_CODE_160);
			}else{
				$macro[outcome_response]=OUTCOME_OK;
			}
			$macro[type_operation]=$from_client->type_operation;
			$macro[PK_SESSION]=$pk_session;
			$macro[STX_USERID_CASE_SENSITIVE]=$name;
			echo json_encode($macro);
		}else{
			$query ="UPDATE USER SET QTY_LAST_FAILED_TENTATIVES = QTY_LAST_FAILED_TENTATIVES + 1 " .
					" WHERE LOWER(MTX_EMAIL) = LOWER('$from_client->MTX_EMAIL')";

			$retval = $mysqli->query($query);
			
			throw new Exception(NO_LOGIN_156);
		}
	}
	
	/*
	 * LOGOUT
	 * 
	 */
	function logout ($from_client, $mysqli){
		$time_now=time();
		$query="UPDATE SESSION SET TMS_END = $time_now, FLC_STATUS='C' WHERE PK_SESSION = $from_client->PK_SESSION ";
		$retval = $mysqli->query($query);
		//nessuna gestione dell'errore
		$macro=array();
		$macro[outcome_response]=OUTCOME_OK;
		$macro[type_operation]=$from_client->type_operation;
		echo json_encode($macro);
	}
	
	/*
	 * REGISTRATION (CODE)
	 * 
	 */
	function unlock_code_registration($from_client, $mysqli){
		$query="SELECT INT_KEY_REGISTER, PK_USER FROM USER " .
				" WHERE LOWER(MTX_EMAIL) = LOWER('$from_client->MTX_EMAIL') AND STX_MD5_PASSWORD='$from_client->STX_MD5_PASSWORD'";
		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		
		$fetch=$retval->fetch_assoc();
		
		if ($fetch){
			$key=$fetch["INT_KEY_REGISTER"];
			$pk_userid=$fetch["PK_USER"];
			if ($key == $from_client->INT_KEY_REGISTER){
				$query ="UPDATE USER SET FLC_NEED_CODE = 'N' WHERE PK_USER = $pk_userid";
				$retval = $mysqli->query($query);
				$macro=array();
				$macro[outcome_response]=OUTCOME_OK;
				$macro[type_operation]=$from_client->type_operation;
				echo json_encode($macro);
			}else{
				$query ="UPDATE USER SET QTY_LAST_FAILED_TENTATIVES = QTY_LAST_FAILED_TENTATIVES + 1 " .
						" WHERE PK_USER = $pk_userid";
				
				$retval = $mysqli->query($query);
				$mysqli->commit();
				throw new Exception(WRONG_CODE_157);
			}
		}else{
			$query ="UPDATE USER SET QTY_LAST_FAILED_TENTATIVES = QTY_LAST_FAILED_TENTATIVES + 1 " .
					" WHERE LOWER(MTX_EMAIL) = LOWER('$from_client->MTX_EMAIL') ";
			
			$retval = $mysqli->query($query);
			$mysqli->commit();
			throw new Exception(NO_LOGIN_156);
		}
	}
	
	/*
	 * COMMIT --> MESSAGE
	 * 
	 */
	function commit_message($from_client, $mysqli){
		
		//certifica che la sessione inviata dal client sia legata effettivamente a questo utente e che sia l'ultima sessione attiva
		$query="SELECT U.PK_USER FROM " .
				" (SELECT FK_USER, FLC_STATUS FROM SESSION WHERE PK_SESSION=$from_client->PK_SESSION ) AS S, USER AS U " .
				" WHERE S.FK_USER = U.PK_USER AND U.FK_LAST_SESSION = $from_client->PK_SESSION AND S.FLC_STATUS = 'O' ";
		
		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		
		$fetch=$retval->fetch_assoc();
		
		if (!$fetch){
			throw new Exception(SESSION_NOT_FOUND_103);
		}
		
		$pk_user=$fetch["PK_USER"];
		$time_now=time();
		$query="INSERT INTO MESSAGE (TMS_CREATED, TMS_LAST_MESSAGE_UPDATE, FK_USER_IDX, LNG_LATITUDE_IDX, LNG_LONGITUDE_IDX, LTX_MESSAGE, TMS_LENGTH_VALIDITY)" .
				" VALUES ($time_now, $time_now, $pk_user, $from_client->LNG_LATITUDE_IDX, $from_client->LNG_LONGITUDE_IDX, '$from_client->LTX_MESSAGE', $from_client->ms_up_to_expiration )";

		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		$pk_message=$mysqli->insert_id;
		$macro=array();
		$macro[outcome_response]=OUTCOME_OK;
		$macro[type_operation]=$from_client->type_operation;
		$macro[PK_MESSAGE]=$pk_message;
		$macro[TMS_CREATED]=$time_now;
		$macro[id_track_commit]=$from_client->id_track_commit;
		echo json_encode($macro);
		
	}
	
	/*
	 * COMMIT --> COMMENT
	 * 
	 */
	function commit_comment($from_client, $mysqli){
		
		//certifica che la sessione inviata dal client sia legata effettivamente a questo utente e che sia l'ultima sessione attiva
		$query="SELECT U.PK_USER FROM " .
				" (SELECT FK_USER, FLC_STATUS FROM SESSION WHERE PK_SESSION=$from_client->PK_SESSION ) AS S, USER AS U " .
				" WHERE S.FK_USER = U.PK_USER AND U.FK_LAST_SESSION = $from_client->PK_SESSION AND S.FLC_STATUS = 'O' ";
		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		
		$fetch=$retval->fetch_assoc();
		
		if (!$fetch){
			throw new Exception(SESSION_NOT_FOUND_103);
		}
		
		$pk_user=$fetch["PK_USER"];
		$time_now=time();
		$query="INSERT INTO COMMENT (TMS_CREATED, TMS_LAST_COMMENT_UPDATE, FK_MESSAGE_IDX, LTX_COMMENT, FK_USER) " .
				" VALUES ($time_now, $time_now, $from_client->FK_MESSAGE, '$from_client->LTX_COMMENT', $pk_user )";

		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		$pk_comment=$mysqli->insert_id;
		$macro=array();
		$macro[outcome_response]=OUTCOME_OK;
		$macro[type_operation]=$from_client->type_operation;
		$macro[PK_COMMENT]=$pk_comment;
		$macro[FK_MESSAGE]=$from_client->FK_MESSAGE;
		$macro[TMS_CREATED]=$time_now;
		$macro[id_track_commit]=$from_client->id_track_commit;
		echo json_encode($macro);
		
	}
	
	/*
	 * COMMIT --> PREFERENCE
	 * 
	 */
	function commit_preference($from_client, $mysqli){

		//certifica che la sessione inviata dal client sia legata effettivamente a questo utente e che sia l'ultima sessione attiva
		$query="SELECT U.PK_USER FROM " .
				" (SELECT FK_USER, FLC_STATUS FROM SESSION WHERE PK_SESSION=$from_client->PK_SESSION ) AS S, USER AS U " .
				" WHERE S.FK_USER = U.PK_USER AND U.FK_LAST_SESSION = $from_client->PK_SESSION AND S.FLC_STATUS = 'O' ";
		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		
		$fetch=$retval->fetch_assoc();
		
		if (!$fetch){
			throw new Exception(SESSION_NOT_FOUND_103);
		}
		
		$pk_user=$fetch["PK_USER"];
		$time_now=time();
		$fk_message=$from_client->PK_MESSAGE;
		$fk_comment=$from_client->PK_COMMENT;
		$table="";
		$limit="";
		$set = "";
		$where_cond="";
		
		$foundIt1=false;
		$foundIt2=false;
		
		if (strcasecmp($from_client->where_preference, PREFERENCE_COMMENT)== 0){
			$table=" COMMENT ";
			$set = " TMS_LAST_COMMENT_UPDATE = $time_now ";
			$pk=$fk_comment;
			$foundIt1=true;
		}else if (strcasecmp($from_client->where_preference, PREFERENCE_MESSAGE)== 0){
			$set = " TMS_LAST_MESSAGE_UPDATE = $time_now ";
			$table=" MESSAGE ";
			$fk_comment=-1;
			$pk=$fk_message;
			$foundIt1=true;
		}
		
		$suffix="_".trim($table);
		if (strcasecmp($from_client->add_less_like_unlike, ADD_LIKE) == 0){
			$foundIt2=true;
			$limit=" 1=1 ";
			$set .=", QTY_LIKE$suffix = QTY_LIKE$suffix + 1 ";	
		}else if (strcasecmp($from_client->add_less_like_unlike, ADD_UNLIKE) == 0){
			$foundIt2=true;
			$limit=" 1=1 ";
			$set .=", QTY_UNLIKE$suffix = QTY_UNLIKE$suffix + 1 ";	
		}else if (strcasecmp($from_client->add_less_like_unlike, LESS_UNLIKE) == 0 ){
			$foundIt2=true;
			$limit=" QTY_UNLIKE$suffix > 0 ";
			$set .=", QTY_UNLIKE$suffix = QTY_UNLIKE$suffix - 1 ";	
		}else if (strcasecmp($from_client->add_less_like_unlike, LESS_LIKE) == 0){
			$foundIt2=true;
			$limit=" QTY_LIKE$suffix > 0 ";
			$set .=", QTY_LIKE$suffix = QTY_LIKE$suffix - 1 ";	
		}
		
		$where_cond=" PK$suffix = $pk AND $limit ";
		
		if ( !($foundIt1 && $foundIt2) ){
			throw new Exception(str_replace("??ERROR??", "($from_client->add_less_like_unlike) ($from_client->where_preference)" ,COMMAND_UNKNOWN_ERR_104));
		}
		
		//USER_PREFERENCE
		$fix_user_preference=false;
		if (strcasecmp($from_client->add_less_like_unlike, ADD_LIKE) == 0){
			$query="INSERT INTO USER_PREFERENCE (TMS_LAST_UPDATE, FLC_LIKE_UNLIKE, FK_MESSAGE, FK_COMMENT, FK_USER) " .
					" VALUES ($time_now, 'L', $fk_message, $fk_comment, $pk_user ) on duplicate key update FLC_LIKE_UNLIKE='L', TMS_LAST_UPDATE=$time_now  ";
			$retval = $mysqli->query($query);
			$fix_user_preference=true;
		}else if (strcasecmp($from_client->add_less_like_unlike, ADD_UNLIKE) == 0 ){
			$query="INSERT INTO USER_PREFERENCE (TMS_LAST_UPDATE, FLC_LIKE_UNLIKE, FK_MESSAGE, FK_COMMENT, FK_USER) " .
					" VALUES ($time_now, 'U', $fk_message, $fk_comment, $pk_user ) on duplicate key update FLC_LIKE_UNLIKE='U', TMS_LAST_UPDATE=$time_now  ";
			$retval = $mysqli->query($query);
			$fix_user_preference=true;
		}else if(strcasecmp($from_client->add_less_like_unlike, LESS_LIKE) == 0 || strcasecmp($from_client->add_less_like_unlike, LESS_UNLIKE) == 0 ){
			$query="DELETE USER_PREFERENCE WHERE FK_MESSAGE=$fk_message AND FK_COMMENT=$fk_comment AND FK_USER=$pk_user ";
			$retval = $mysqli->query($query);
			$fix_user_preference=true;
		}
		
		//COMMENT/MESSAGE
		if ($fix_user_preference){	//tutto ok l'inserimento
			$query ="UPDATE $table SET $set WHERE $where_cond ";
			$retval = $mysqli->query($query);
			if ($retval){	//tutto ok l'inserimento
				$macro=array();
				$macro[outcome_response]=OUTCOME_OK;
				$macro[type_operation]=$from_client->type_operation;
				$macro[id_track_commit]=$from_client->id_track_commit;
				echo json_encode($macro);
				return;
			}
		}
		throw new Exception(PREFERENCE_ERROR_161);
	}
	
	/*
	 * REFRESH
	 * 
	 */
	function refresh($from_client, $mysqli){

		//certifica che la sessione inviata dal client sia legata effettivamente a questo utente e che sia l'ultima sessione attiva
		$query="SELECT U.PK_USER FROM " .
				" (SELECT FK_USER, FLC_STATUS  FROM SESSION WHERE PK_SESSION=$from_client->PK_SESSION ) AS S, USER AS U " .
				" WHERE S.FK_USER = U.PK_USER AND U.FK_LAST_SESSION = $from_client->PK_SESSION AND S.FLC_STATUS = 'O' ";
		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		
		$fetch=$retval->fetch_assoc();
		
		if (!$fetch){
			throw new Exception(SESSION_NOT_FOUND_103);
		}
		
		$pk_user=$fetch["PK_USER"];
		$last_macro_bound_refresh= $fetch["PK_USER"];
		$query="SELECT m.*, um.STX_USERID_CASE_SENSITIVE AS USERID_MESSAGE, " .
				"    IF (pm.FLC_LIKE_UNLIKE IS NULL, '*', pm.FLC_LIKE_UNLIKE ) AS LK_UK_MSG, " .
				"    c.*, uc.STX_USERID_CASE_SENSITIVE AS USERID_COMMENT, " .
				"    IF (pc.FLC_LIKE_UNLIKE IS NULL, '*', pc.FLC_LIKE_UNLIKE ) AS LK_UK_CMNT " .
				" FROM MESSAGE AS m LEFT JOIN USER_PREFERENCE AS pm ON m.PK_MESSAGE=pm.FK_MESSAGE AND pm.FK_COMMENT = -1 AND pm.FK_USER=$pk_user AND m.TMS_LAST_MESSAGE_UPDATE > $from_client->LNG_LAST_MACRO_BOUND_REFRESH AND UPPER(m.FLC_IS_VALID) = 'Y' " .
				"    LEFT JOIN COMMENT AS c ON m.PK_MESSAGE=c.FK_MESSAGE_IDX AND c.TMS_LAST_COMMENT_UPDATE > $from_client->LNG_LAST_MACRO_BOUND_REFRESH " .
				"    LEFT JOIN USER_PREFERENCE pc ON c.PK_COMMENT=pc.FK_COMMENT AND c.FK_MESSAGE_IDX=pc.FK_MESSAGE AND pc.FK_USER=$pk_user " .
				"    JOIN USER um ON m.FK_USER_IDX=um.PK_USER " .
				"    JOIN USER uc ON c.FK_USER=uc.PK_USER" .
				" WHERE $from_client->left_bound_latitude < m.LNG_LATITUDE_IDX AND $from_client->right_bound_latitude > m.LNG_LATITUDE_IDX " .
				"    AND $from_client->bottom_bound_latitude < m.LNG_LONGITUDE_IDX AND $from_client->top_bound_latitude > m.LNG_LONGITUDE_IDX ";
			
		$retval = $mysqli->query($query);
		if (! $retval){
			throw new Exception(str_replace("??ERROR??", $mysqli->error ,QUERY_ERR_102));
		}
		
		$macro=array();
		$macro[messages]=array();
		$macro[comments]=array();
		$pk_message=-1;
		$time_now=time();
		$max_time=$from_client->LNG_LAST_MACRO_BOUND_REFRESH;
		while ($fetch = $retval->fetch_assoc()) {
        	if ($pk_message!=$fetch["PK_MESSAGE"]){
        		$pk_message=$fetch["PK_MESSAGE"];
        		$max_time = max($max_time, $fetch["TMS_LAST_MESSAGE_UPDATE"]);
        		$message1=array();
				$message1[PK_MESSAGE] = $fetch["PK_MESSAGE"];
				$message1[TMS_CREATED] = $fetch["TMS_CREATED"];
				$message1[TMS_LAST_MESSAGE_UPDATE] = $fetch["TMS_LAST_MESSAGE_UPDATE"];
				$message1[LNG_LATITUDE_IDX] = $fetch["LNG_LATITUDE_IDX"];
				$message1[LNG_LONGITUDE_IDX] = $fetch["LNG_LONGITUDE_IDX"];
				$message1[QTY_LIKE] = $fetch["QTY_LIKE_MESSAGE"];
				$message1[QTY_UNLIKE] = $fetch["QTY_UNLIKE_MESSAGE"];
				$message1[i_liked_unliked_this] = $fetch["LK_UK_MSG"];
				$message1[STX_USERID_CASE_SENSITIVE] = $fetch["USERID_MESSAGE"];
				$message1[ms_up_to_expiration] = $fetch["TMS_CREATED"] + $fetch["TMS_LENGTH_VALIDITY"] - $time_now;
				$message1[LTX_MESSAGE] = $fetch["LTX_MESSAGE"];
				array_push($macro[messages], $message1);
        	}
        	if ($fetch["PK_COMMENT"] != null){
        		$max_time = max($max_time, $fetch["TMS_LAST_COMMENT_UPDATE"]);
        		$comment1=array();
				$comment1[PK_COMMENT] = $fetch["PK_COMMENT"];
				$comment1[FK_MESSAGE] = $pk_message;
				$comment1[TMS_CREATED] = $fetch["TMS_CREATED"];
				$comment1[TMS_LAST_COMMENT_UPDATE] = $fetch["TMS_LAST_COMMENT_UPDATE"];
				$comment1[QTY_LIKE] = $fetch["QTY_LIKE_COMMENT"];
				$comment1[QTY_UNLIKE] = $fetch["QTY_UNLIKE_COMMENT"];
				$comment1[i_liked_unliked_this]= $fetch["LK_UK_CMNT"];
				$comment1[STX_USERID_CASE_SENSITIVE] = $fetch["USERID_COMMENT"];
				$comment1[LTX_COMMENT] = $fetch["LTX_COMMENT"];
				array_push($macro[comments], $comment1);
        	}
    	}
		$macro[outcome_response]=OUTCOME_OK;
		$macro[type_operation]=$from_client->type_operation;
		$macro[LNG_LAST_MACRO_BOUND_REFRESH]=$max_time;
		echo json_encode($macro);
	}
	
	/*
	 * PING
	 * 
	 */
	
	function ping($from_client){
		$timenow=time();
		$macro=array();
		$macro[outcome_response]=OUTCOME_OK;
		$macro[type_operation]=$from_client->type_operation;
		$macro["test1"]=date("F j, Y, g:i a", $timenow);
		$macro["test2"]=$timenow;
		echo json_encode($macro);
	}

?>	